package com.tl.web05.dao;

import com.tl.web05.model.entity.User;
import com.tl.web05.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库持久类,用户持久类
 *
 * @author Antg
 * @date 2021/7/3018:57
 */
public class UserDao {
    /**
     * 添加用户
     *
     * @author Antg
     * @date 2021/7/30 18:58
     */
    public void add(User user) throws SQLException {
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "insert into user(username, password, nickname, age, birthday) value (?,?,?,?,?)";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //配置参数
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getNickname());
        ps.setInt(4, user.getAge());
        ps.setDate(5, new Date(user.getBirthday().getTime()));
        //执行sql
        ps.execute();
        DBUtil.release(connection, ps);
    }

    /**
     * 判断用户名是否存在,存在返回true,不存在返回false
     *
     * @author Antg
     * @date 2021/7/30 19:10
     */
    public boolean existUsername(String username) throws SQLException {
        int num = 0;
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "select count(1) as num from user where username = ?";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, username);
        //执行
        ResultSet rs = ps.executeQuery();
        rs.next();
        num = rs.getInt("num");
        DBUtil.release(connection, ps, rs);
        return num > 0;
    }

    /**
     * 根据用户名和密码查找用户
     *
     * @author Antg
     * @date 2021/7/30 19:18
     */
    public User getUserByUserNameAndPassword(String username, String password) throws SQLException {
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "select * from user where username = ? and password = ?";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, username);
        ps.setString(2, password);
        //执行sql
        ResultSet rs = ps.executeQuery();
        rs.next();
        if (rs.getRow() == 0) {
            return null;
        }
        int r_id = rs.getInt("id");
        String r_username = rs.getString("username");
        String r_password = rs.getString("password");
        String r_nickname = rs.getString("nickname");
        int r_age = rs.getInt("age");
        Date r_birthday = rs.getDate("birthday");
        User user = new User(r_id, r_username, r_password, r_nickname, r_age, r_birthday);
        return user;
    }

    /**
     * 根据用户名查询用户
     *
     * @author Antg
     * @date 2021/7/30 20:28
     */
    public User getUserByUserName(String username) throws SQLException {
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "select * from user where username = ?";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, username);
        //执行sql
        ResultSet rs = ps.executeQuery();
        rs.next();
        if (rs.getRow() == 0) {
            return null;
        }
        int r_id = rs.getInt("id");
        String r_username = rs.getString("username");
        String r_password = rs.getString("password");
        String r_nickname = rs.getString("nickname");
        int r_age = rs.getInt("age");
        Date r_birthday = rs.getDate("birthday");
        User user = new User(r_id, r_username, r_password, r_nickname, r_age, r_birthday);
        return user;
    }

    /**
     * 获取表中所有数据
     *
     * @return
     * @throws SQLException
     */
    public List<User> getAllUser() throws SQLException {
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "select * from user";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //执行sql语句
        ResultSet rs = ps.executeQuery();
        //处理结果集
        List<User> userList = new ArrayList<>();
        while (rs.next()) {
            if (rs.getRow() == 0) {
                return null;
            }
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setNickname(rs.getString("nickname"));
            user.setAge(rs.getInt("age"));
            user.setBirthday(rs.getDate("birthday"));
            userList.add(user);
        }
        return userList;
    }

    /**
     * 根据用户名删除用户,如果没有该用户返回false
     *
     * @author Antg
     * @date 2021/7/30 20:34
     */
    public boolean delUser(String username) throws SQLException {
        //删除之前判断一下该用户是否存在
        if (!this.existUsername(username)) {
            return false;
        }
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "delete from user where username = ?";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, username);
        //执行sql
        ps.execute();
        return true;
    }

    /**
     * 编辑用户
     *
     * @author Antg
     * @date 2021/7/30 20:53
     */
    public void modifyUser(User user) throws SQLException {
        //获取数据库连接
        Connection connection = DBUtil.getConnection();
        //创建sql模板
        String sql = "update user set password = ? , nickname = ? ,age = ? , birthday = ? where username = ?";
        //获取会话
        PreparedStatement ps = connection.prepareStatement(sql);
        //设置参数
        ps.setString(1, user.getPassword());
        ps.setString(2, user.getNickname());
        ps.setInt(3, user.getAge());
        ps.setDate(4, new Date(user.getBirthday().getTime()));
        ps.setString(5, user.getUsername());
        //执行sql
        ps.execute();
    }
}
